﻿
Imports System.Collections
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient

Namespace DAO
    Public Class connect
        '  Private str_Connection As String = "server=.;database=QuanLyTiecCuoi;uid=sa;pwd= ;integrated security=SSPI;Connect Timeout=5"
        Private str_Connection As String = "Data Source=JANGNGUYEN-PC\SQLEXPRESS;Initial Catalog=QuanLyTiecCuoi;Integrated Security=True"
        Public con As SqlConnection
       
        Public Sub New()
        End Sub
        Public Sub CloseConnection()
            con.Close()
        End Sub
        Public Sub OpenConnection()

            con = New SqlConnection(str_Connection)
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If

        End Sub
        '      public bool TestConnection()
        '        {
        '            SqlConnection test_Con = new SqlConnection(str_Connection);
        '            try
        '            {
        '                test_Con.Open();
        '                
        '                test_Con.Close();
        '                msgSQL.Close();
        '                return true;
        '            }
        '            catch
        '            {
        '                //				MessageBox.Show("Không kết nối được với SQL Server!!!","Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
        '               
        '                return false;
        '            }
        '        }


        Public ReadOnly Property getConnection() As SqlConnection
            Get
                Return con
            End Get
        End Property
        Public Function getDataset(ByVal str As String) As DataSet
            Dim da As New SqlDataAdapter(str, con)
            Dim ds As New DataSet()
            da.Fill(ds)
            Return ds
        End Function
        Public Function getDataTable(ByVal sql As String) As DataTable
            Dim da As New SqlDataAdapter(sql, getConnection)
            Dim dt As New DataTable()
            da.Fill(dt)
            Return dt
        End Function

        Public Function ExecuteDataTable(ByVal sql As String, ByVal commandType As CommandType, ByVal ParamArray pars As Object()) As DataTable
            Dim con As New SqlConnection(str_Connection)

            Dim com As New SqlCommand(sql, con)
            com.CommandType = commandType

            For i As Integer = 0 To pars.Length - 1 Step 2
                Dim par As New SqlParameter(pars(i).ToString(), pars(i + 1))
                com.Parameters.Add(par)
            Next

            Dim dad As New SqlDataAdapter(com)

            Dim dst As New DataSet()
            dad.Fill(dst)

            Return dst.Tables(0)
        End Function

    End Class

End Namespace
